package org.tulkas.log;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.tulkas.base.Constants;
import org.tulkas.exception.SystemException;
import org.tulkas.util.FileUtil;
import org.tulkas.util.LangUtil;
import org.tulkas.util.SqlUtil;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * @author <a href="mailto:tulkas.web@gmail.com">Tulkas</a>
 *
 */
public class LogDao {
	private final ComboPooledDataSource datasource;

	public LogDao(ComboPooledDataSource datasource) {
		this.datasource = datasource;
	}

	public DataSource getDatasource() {
		return datasource;
	}
	
	public void destroy() {
		datasource.close();
	}

	public boolean isTablesExist() {
		try {
			JdbcTemplate jdbc = new JdbcTemplate(datasource);
			jdbc.queryForLong("select max(id) from log_main");
		} catch (DataAccessException e) {
			return false;
		}
		return true;
	}

	public void dropTables() {
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		try {
			Reader reader = new InputStreamReader(this.getClass()
					.getResourceAsStream("/META-INF/ddl/log-drop.sql"),
					Constants.DEFAULT_CHARSET);
			String str = FileUtil.readString(reader);
			String[] sqls = SqlUtil.parseScript(str);
			for (String sql : sqls) {
				try {
					jdbc.execute(sql);
				} catch (DataAccessException e) {
					//ignore
				}
			}
		} catch (UnsupportedEncodingException e) {
			throw new SystemException(e);
		} catch (IOException e) {
			throw new SystemException(e);
		}
	}

	public void createTables() {
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		try {
			Reader reader = new InputStreamReader(this.getClass()
					.getResourceAsStream("/META-INF/ddl/log-create.sql"),
					Constants.DEFAULT_CHARSET);
			String str = FileUtil.readString(reader);
			String[] sqls = SqlUtil.parseScript(str);
			jdbc.batchUpdate(sqls);
		} catch (UnsupportedEncodingException e) {
			throw new SystemException(e);
		} catch (IOException e) {
			throw new SystemException(e);
		}
	}

	public LogRecord getLogById(long id) {
		String sql = "select * from log_main where id=?";
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		List<LogRecord> list = jdbc.query(sql, prepareRowMapper(), id);
		LogRecord r = null;
		if (list.size() > 0) {
			r = list.get(0);
			if (r.getLevel() == LogRecord.LEVEL_ERROR) {
				String ex = jdbc.queryForObject(
						"select stack_trace from log_exception where log_id=?",
						String.class, id);
				r.setExceptionStackTrace(ex);
			}
		}
		return r;
	}

	private RowMapper<LogRecord> prepareRowMapper() {
		RowMapper<LogRecord> mapper = new RowMapper<LogRecord>() {

			public LogRecord mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				LogRecord r = new LogRecord();
				r.setId(rs.getLong("ID"));
				r.setLevel(rs.getInt("LEVEL"));
				r.setLogType(rs.getString("LOG_TYPE"));
				r.setMessage(rs.getString("MESSAGE"));
				r.setName(rs.getString("NAME"));
				r.setThreadName(rs.getString("THREAD_NAME"));
				r.setTime(rs.getTimestamp(rs.getString("LOG_TIME")));
				return r;
			}
		};

		return mapper;
	}

	private String prepareQueryBaseCond(LogQuery query,
			Map<String, Object> paramMap) {
		int[] levels = query.getLevels();
		paramMap.put("levels", levels);

		String logTypeClause = "";
		if (query.getLogType() != null && !"".equals(query.getLogType())) {
			logTypeClause = " and log_type=:logType";
			paramMap.put("logType", query.getLogType());
		}
		String keywordClause = "";
		if (query.getKeyword() != null && !"".equals(query.getKeyword())) {
			keywordClause = " and message like '%:keyword%'";
			paramMap.put("keyword", query.getKeyword());
		}
		String timeEndClause = "";
		if (query.getTimeEnd() != null) {
			timeEndClause = " and log_time<=:timeEnd";
			paramMap
					.put("timeEnd", new Timestamp(query.getTimeEnd().getTime()));
		}

		String sqlCond = "from log_main where level in :levels" + logTypeClause
				+ keywordClause + timeEndClause;
		return sqlCond;
	}

	public LogRecord[] queryLogs(LogQuery query, int count) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String baseCond = prepareQueryBaseCond(query, paramMap);

		String timeStartClause = "";
		if (query.getTimeStart() != null) {
			timeStartClause = " and log_time>=:timeStart";
			paramMap.put("timeStart", new Timestamp(query.getTimeStart()
					.getTime()));
		}

		String sqlFetch = " fetch first " + count + " rows only";
		String sqlTest = "select log_time " + baseCond + timeStartClause
				+ " order by log_time desc" + sqlFetch;

		NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(
				datasource);
		Timestamp ts = jdbc.queryForObject(sqlTest, paramMap, Timestamp.class);
		if (ts != null) {
			timeStartClause = " and log_time>=:timeStart";
			paramMap.put("timeStart", ts);
		}
		final String sql = "select * " + baseCond + timeStartClause
				+ " order by log_time asc" + sqlFetch;
		List<LogRecord> list = jdbc.query(sql, paramMap, prepareRowMapper());
		if (list != null) {
			return list.toArray(new LogRecord[list.size()]);
		} else {
			return null;
		}
	}

	public boolean insertLog(final LogRecord record) {
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		final String sql = "insert into log_main (level , message , name, log_type, thread_name, log_time) values (?,?,?,?,?,?)";
		if (record.getTime() == null) {
			record.setTime(new Date());
		}
		final Timestamp ts = new Timestamp(record.getTime().getTime());
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setLong(1, record.getLevel());
				ps
						.setString(2, LangUtil.ensureLength(
								record.getMessage(), 512));
				ps.setString(3, LangUtil.ensureLength(record.getName(), 128));
				ps.setString(4, LangUtil.ensureLength(record.getLogType(), 64));
				ps.setString(5, LangUtil.ensureLength(record.getThreadName(),
						64));
				ps.setTimestamp(6, ts);
				return ps;
			}
		};
		KeyHolder kh = new GeneratedKeyHolder();
		int rows = jdbc.update(psc, kh);
		Number key = kh.getKey();
		if (key != null) {
			record.setId(key.longValue());
		}
		return rows == 1;
	}

	public boolean removeLogById(long id) {
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		int rows = jdbc.update("delete from log_main where id=?", id);
		return rows == 1;
	}

	public String getStaceTraceById(long id) {
		JdbcTemplate jdbc = new JdbcTemplate(datasource);
		return jdbc.queryForObject(
				"select stack_trace from log_exception where log_id=?",
				String.class, id);
	}

	public int removeLogs(LogQuery query) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String baseCond = prepareQueryBaseCond(query, paramMap);

		String timeStartClause = "";
		if (query.getTimeStart() != null) {
			timeStartClause = " and log_time>=:timeStart";
			paramMap.put("timeStart", new Timestamp(query.getTimeStart()
					.getTime()));
		}

		String sqlRemove = "delete " + baseCond + timeStartClause;
		NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(
				datasource);
		return jdbc.update(sqlRemove, paramMap);
	}
}
